package com.hcrazy.manchester.app.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;

import com.hcrazy.manchester.app.pojo.Friend;
import com.hcrazy.manchester.app.pojo.PageModel;
import com.hcrazy.manchester.app.pojo.User;
import com.hcrazy.manchester.app.pojo.UserInfo;

public class UserDAO extends BaseDAO<User>{

	public boolean isRegisteredEmail(String email) throws Exception{
		String sql = "select count(*) from him_user where email = ?";
		int i = this.getJdbcTemplate().queryForInt(sql, email);
		return (i > 0);
	}
	
	public boolean isRegisteredPhone(String phone) throws Exception{
		String sql = "select count(*) from him_user where phone = ?";
		int i = this.getJdbcTemplate().queryForInt(sql, phone);
		return (i > 0);
	}
	
	public void addUser(User user) throws Exception{
		StringBuilder sb = new StringBuilder();
		sb.append("insert into him_user(");
		sb.append("username,nickname,email,phone,password,registerTime,remoteIp,type,memberType,couponBag,bagCount,exp,groupId,groupName");
		sb.append(") values (");
		sb.append(":username,:nickname,:email,:phone,:password,:registerTime,:remoteIp,:type,:memberType,:couponBag,:bagCount,:exp,:groupId,:groupName");
		sb.append(")");
		
		update(sb.toString(), user);
	}
	
	public void addCollege(long userId,long infoId,int type) throws Exception{
		String sql = "insert into him_user_collection (userId,infoId,type) values(?,?,?)";
		this.commonUpdate(sql, userId,infoId,type);
	}
	
	public int getCollegeCount(long userId,long infoId,int type) throws Exception{
		String sql = "select count(*) from him_user_collection where userId = ? and infoId = ? and type = ?";
		return getCount(sql, userId,infoId,type);
	}
	
	public void updatePass(long userId,String password) throws Exception {
		String sql = "update him_user set password = ? where userId = ?";
		this.commonUpdate(sql, password,userId);
	}
	
	public void updateUser(User user) throws Exception{
		StringBuilder sb = new StringBuilder();
		sb.append("update him_user set ");
		sb.append(" username=:username,email=:email,phone=:phone,password=:password,couponBag = :couponBag,bagCount=:bagCount");
		sb.append(" where userId = :userId");
		
		update(sb.toString(), user);
	}
	
	public void addUserInfo(UserInfo info) throws Exception{
		StringBuilder sb = new StringBuilder();
		sb.append("insert into him_user_info(");
		sb.append("userId,name,gender,workPlace,birthday,header,description_mc,");
		sb.append("provinceId,cityId,districtId");
		sb.append(",status,school,grade,toSchoolYear,homeTown,specialty,address) values (");
		sb.append(":userId,:name,:gender,:workPlace,:birthday,:header,:description_mc,");
		sb.append(":provinceId,:cityId,:districtId");
		sb.append(",:status,:school,:grade,:toSchoolYear,:homeTown,:specialty,:address)");
		
		update(sb.toString(), info);
	}
	
	public void updateUserInfo(UserInfo info) throws Exception{
		StringBuilder sb = new StringBuilder();
		sb.append("update him_user_info set ");
		sb.append(" name=:name,gender=:gender,workPlace=:workPlace,birthday=:birthday,specialty=:specialty,address=:address,");
		sb.append(" provinceId=:provinceId,cityId=:cityId,districtId=:districtId,");
		sb.append(" header=:header,description_mc=:description_mc,status=:status,school=:school,toSchoolYear=:toSchoolYear,grade=:grade,homeTown=:homeTown");
		sb.append(" where userId = :userId");
		
		update(sb.toString(), info);
	}
	
	public void updateHeader(String header,long userId) throws Exception{
		StringBuilder sb = new StringBuilder();
		sb.append("update him_user_info set ");
		sb.append(" header=?");
		sb.append(" where userId = ?");
		
		commonUpdate(sb.toString(), header,userId);
	}
	
	public UserInfo getUserInfo(long userId) throws Exception{
		String sql = "select * from him_user_info a,him_user b where a.userId = b.userId and a.userId = ?";
		return this.getJdbcTemplate().queryForObject(sql,new Object[]{userId},new RowMapper<UserInfo>(){
			@Override
			public UserInfo mapRow(ResultSet rs, int arg1) throws SQLException {
				UserInfo info = new UserInfo();
				info.setBirthday(rs.getString("birthday"));
				info.setDescription_mc(rs.getString("description_mc"));
				info.setGender(rs.getInt("gender"));
				info.setHeader(rs.getString("header"));
				info.setHomeTown(rs.getString("homeTown"));
				info.setName(rs.getString("name"));
				info.setSchool(rs.getInt("school"));
				info.setGrade(rs.getInt("grade"));
				info.setStatus(rs.getInt("status"));
				info.setToSchoolYear(rs.getString("toschoolyear"));
				info.setUserId(rs.getLong("userId"));
				info.setWorkPlace(rs.getString("workplace"));
				info.setSpecialty(rs.getString("specialty"));
				info.setAddress(rs.getString("address"));
				info.setProvinceId(rs.getInt("provinceId"));
				info.setCityId(rs.getInt("cityId"));
				info.setDistrictId(rs.getInt("districtId"));
				
				User user = new User();
				user.setAddress(rs.getString("address"));
				user.setUsername(rs.getString("username"));
				user.setActived(rs.getInt("actived"));
				user.setBagCount(rs.getInt("bagCount"));
				user.setCouponBag(rs.getInt("couponBag"));
				user.setEmail(rs.getString("email"));
				user.setExp(rs.getInt("exp"));
				user.setGroupId(rs.getInt("groupId"));
				user.setGroupName(rs.getString("groupName"));
				user.setNickname(rs.getString("nickname"));
				user.setPhone(rs.getString("phone"));
				user.setRegisterTime(rs.getString("registerTime"));
				
				info.setUser(user);
				
				return info;
			}
		});
	}
	
	public List<UserInfo> getUserInfosByHomeTown(String homeTown) throws Exception{
		String sql = "select a.*,b.username from him_user_info a,him_user b where a.userId = b.userId and a.homeTown = ?";
		return this.getJdbcTemplate().query(sql,new Object[]{homeTown},new RowMapper<UserInfo>(){
			@Override
			public UserInfo mapRow(ResultSet rs, int arg1) throws SQLException {
				UserInfo info = new UserInfo();
				info.setBirthday(rs.getString("birthday"));
				info.setDescription_mc(rs.getString("description_mc"));
				info.setGender(rs.getInt("gender"));
				info.setHeader(rs.getString("header"));
				info.setHomeTown(rs.getString("homeTown"));
				info.setName(rs.getString("name"));
				info.setSchool(rs.getInt("school"));
				info.setGrade(rs.getInt("grade"));
				info.setStatus(rs.getInt("status"));
				info.setToSchoolYear(rs.getString("toschoolyear"));
				info.setUserId(rs.getLong("userId"));
				info.setWorkPlace(rs.getString("workplace"));
				info.setUsername(rs.getString("username"));
				info.setSpecialty(rs.getString("specialty"));
				info.setAddress(rs.getString("address"));
				info.setProvinceId(rs.getInt("provinceId"));
				info.setCityId(rs.getInt("cityId"));
				info.setDistrictId(rs.getInt("districtId"));
				return info;
			}
		});
	}
	
	public List<UserInfo> getUserInfosByCityId(int cityId) throws Exception{
		String sql = "select a.*,b.username,c.cityName,d.provinceName " +
				"from him_user_info a,him_user b,him_town_city c,him_town_province d" +
				" where a.userId = b.userId and a.cityId = ? and a.cityId = c.cityId and c.provinceId = d.provinceId";
		return this.getJdbcTemplate().query(sql,new Object[]{cityId},new RowMapper<UserInfo>(){
			@Override
			public UserInfo mapRow(ResultSet rs, int arg1) throws SQLException {
				UserInfo info = new UserInfo();
				info.setBirthday(rs.getString("birthday"));
				info.setDescription_mc(rs.getString("description_mc"));
				info.setGender(rs.getInt("gender"));
				info.setHeader(rs.getString("header"));
				info.setHomeTown(rs.getString("homeTown"));
				info.setName(rs.getString("name"));
				info.setSchool(rs.getInt("school"));
				info.setGrade(rs.getInt("grade"));
				info.setStatus(rs.getInt("status"));
				info.setToSchoolYear(rs.getString("toschoolyear"));
				info.setUserId(rs.getLong("userId"));
				info.setWorkPlace(rs.getString("workplace"));
				info.setUsername(rs.getString("username"));
				info.setSpecialty(rs.getString("specialty"));
				info.setAddress(rs.getString("address"));
				info.setProvinceId(rs.getInt("provinceId"));
				info.setCityId(rs.getInt("cityId"));
				info.setDistrictId(rs.getInt("districtId"));
				info.setUserId(rs.getLong("userId"));
				return info;
			}
		});
	}
	
	public List<UserInfo> getUserInfosBySchool(int school) throws Exception{
		String sql = "select a.*,b.username from him_user_info a,him_user b where a.userId = b.userId and a.school = ?";
		return this.getJdbcTemplate().query(sql,new Object[]{school},new RowMapper<UserInfo>(){
			@Override
			public UserInfo mapRow(ResultSet rs, int arg1) throws SQLException {
				UserInfo info = new UserInfo();
				info.setBirthday(rs.getString("birthday"));
				info.setDescription_mc(rs.getString("description_mc"));
				info.setGender(rs.getInt("gender"));
				info.setHeader(rs.getString("header"));
				info.setHomeTown(rs.getString("homeTown"));
				info.setName(rs.getString("name"));
				info.setSchool(rs.getInt("school"));
				info.setGrade(rs.getInt("grade"));
				info.setStatus(rs.getInt("status"));
				info.setToSchoolYear(rs.getString("toschoolyear"));
				info.setUserId(rs.getLong("userId"));
				info.setWorkPlace(rs.getString("workplace"));
				info.setUsername(rs.getString("username"));
				info.setSpecialty(rs.getString("specialty"));
				info.setAddress(rs.getString("address"));
				info.setProvinceId(rs.getInt("provinceId"));
				info.setCityId(rs.getInt("cityId"));
				info.setDistrictId(rs.getInt("districtId"));
				return info;
			}
		});
	}
	
	/**
	 * 批量增加好友
	 * @param userId
	 * @param friends
	 * @throws Exception
	 */
	public void addFriends(long userId,String[] friends) throws Exception{
		String sql = "insert into him_user_friend(userId,friendId,status)values(?,?,0)";
		List<Object[]> batchArgs = new ArrayList<Object[]>();
		for(String friend:friends){
			Object[] args = new Object[]{userId,friend};
			batchArgs.add(args);
		}
		this.getJdbcTemplate().batchUpdate(sql, batchArgs);
	}
	
	/**
	 * 单个增加好友
	 * @param userId
	 * @param friendId
	 * @param message
	 * @throws Exception
	 */
	public void addFriend(long userId,long friendId,String message) throws Exception{
		String sql = "insert into him_user_friend(userId,friendId,status,message)values(?,?,0,?)";
		this.commonUpdate(sql, userId,friendId,message);
	}
	
	/**
	 * 保存激活码
	 * @param userId
	 * @throws Exception
	 */
	public void addActiveCode(String email,String activeCode) throws Exception{
		String sql = "insert into him_user_active (email,activeCode) values(?,?)";
		commonUpdate(sql, email,activeCode);
	}
	
	public User getUserByActiveCode(String activeCode) throws Exception{
		String sql = "select a.* from him_user a,him_user_active b where a.email = b.email and b.activeCode = ?";
		return getJavaBean(sql, User.class, activeCode);
	}
	
	public User getUserByEmail(String email) throws Exception {
		String sql = "select * from him_user where email = ? limit 1 ";
		return getJavaBean(sql, User.class, email);
	}
	
	public void setActived(long userId) throws Exception{
		String sql = "update him_user set actived = 1 where userId = ?";
		
		commonUpdate(sql, userId);
	}
	
	public void setFriend(long userId,long friendId,int status) throws Exception{
		String sql = "update him_user_friend set status = ? where userId = ? and friendId = ?";
		commonUpdate(sql, status,userId,friendId);
	}
	
	public User getUserByUsername(String username) throws Exception{
		String sql = "select a.*,b.header,b.provinceId,b.cityId from him_user a,him_user_info b where a.userId = b.userId and a.username = ?";
		return getJavaBean(sql, User.class, username);
	}
	
	public User getUserById(long userId) throws Exception{
		String sql = "select * from him_user where userId = ?";
		return getJavaBean(sql, User.class, userId);
	}
	
	public int getIncreamentId() throws Exception {
		String sql = "select LAST_INSERT_ID() ";
		return this.getJdbcTemplate().queryForInt(sql);
	}
	
	public List<Friend> getFriends(long userId,int status) throws Exception{
		String sql = "select a.*,c.username friendname from him_user_friend a,him_user c where a.userId = ? and a.`status` = ? and a.friendId = c.userId";
		return this.getJdbcTemplate().query(sql,new Object[]{userId,status},new RowMapper<Friend>(){
			@Override
			public Friend mapRow(ResultSet rs, int arg1) throws SQLException {
				Friend f = new Friend();
				f.setFriendId(rs.getLong("friendId"));
				f.setFriendname(rs.getString("friendName"));
				f.setMessage(rs.getString("message"));
				f.setStatus(rs.getInt("status"));
				f.setUserId(rs.getLong("userId"));
				return f;
			}
		});
	}
	
	/**
	 * 获取所有我加过的好友（审核通过、未通过、未审核的）
	 * @param userId
	 * @return
	 * @throws Exception
	 */
	public List<Friend> getMyFriends(long userId) throws Exception{
		String sql = "select * from him_user_friend where userId = ? ";
		return this.getJdbcTemplate().query(sql,new Object[]{userId},new RowMapper<Friend>(){
			@Override
			public Friend mapRow(ResultSet rs, int arg1) throws SQLException {
				Friend f = new Friend();
				f.setFriendId(rs.getLong("friendId"));
				f.setMessage(rs.getString("message"));
				f.setStatus(rs.getInt("status"));
				f.setUserId(rs.getLong("userId"));
				return f;
			}
		});
	}
	
	/**
	 * 后台功能
	 */
	
	/**
	 * 获取带分页的用户列表
	 * @param model
	 * @param search
	 * @return
	 * @throws Exception
	 */
	public PageModel getPagedUsers(PageModel model,String search) throws Exception{
		StringBuilder querySQL = new StringBuilder("select * from him_user ");
		StringBuilder countSQL = new StringBuilder("select count(*) from him_user ");
		StringBuilder whereSQL = new StringBuilder(" where (username like ?) or (phone like ?) or (email like ?) ");
		List<Object> paramList = new ArrayList<Object>();
		paramList.add("%"+search+"%");
		paramList.add("%"+search+"%");
		paramList.add("%"+search+"%");
		return this.getPageModel(model, querySQL, countSQL, whereSQL, paramList, User.class);
	}
	
	public void deleteUser(long userId) throws Exception{
		String sql = "delete from him_user where userId = ?";
		commonUpdate(sql, userId);
	}
	
	public void deleteCollege(long userId,long infoId,int type) throws Exception{
		String sql = "delete from him_user_collection where userId = ? and infoId = ? and type = ?";
		commonUpdate(sql, userId,infoId,type);
	}
	
	public int getBagCount(long userId) throws Exception{
		String sql = "select bagCount from him_user where userId = ?";
		return this.getJdbcTemplate().queryForInt(sql,userId);
	}
	
	public void setUserExp(User user) throws Exception {
		String sql = "update him_user set exp = ?,groupId = ?,groupName = ?,bagCount = ? where userId = ?";
		commonUpdate(sql, user.getExp(),user.getGroupId(),user.getGroupName(),user.getBagCount(),user.getUserId());
	}
	
	/**
	 * 获取全部有城市的会员
	 * @return
	 * @throws Exception
	 */
	public List<UserInfo> getAllUsersHaveCity() throws Exception{
		String sql = "select * from him_user_info where cityId > 0";
		
		return this.getJdbcTemplate().query(sql,new RowMapper<UserInfo>(){
			@Override
			public UserInfo mapRow(ResultSet rs, int arg1) throws SQLException {
				UserInfo info = new UserInfo();
				info.setAddress(rs.getString("userId"));
				info.setProvinceId(rs.getInt("provinceId"));
				info.setCityId(rs.getInt("cityId"));
				info.setDistrictId(rs.getInt("districtId"));
				return info;
			}
		});
	}

	public void updateFriend(long userId, long friendId, String message) throws Exception {
		String sql = "update him_user_friend set status=0,message=? where userId=? and friendId=?";
		this.commonUpdate(sql, message, userId, friendId);
	}
	
	public int getAllCollegeCount(long userId){
		String sql = "select count(*) from him_user_collection where userId =" + userId;
		return getCount(sql);
	}
}
